package com.xlh.service.conclude.impl;

import cn.hutool.cache.CacheUtil;
import cn.hutool.cache.impl.FIFOCache;
import cn.hutool.cache.impl.TimedCache;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.lang.Console;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.math.MathUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.cell.CellUtil;
import com.google.common.collect.Lists;
import com.xlh.common.ResultEnum;
import com.xlh.constant.ConcludeContant;
import com.xlh.dao.conclude.ext.ConcludeCheckSubMapperExt;
import com.xlh.dao.conclude.ext.ConcludeScoreMapperExt;
import com.xlh.dao.conclude.ext.ConcludeStudentMapperExt;
import com.xlh.dao.conclude.ext.ConcludeSubCourseMapperExt;
import com.xlh.dao.conclude.helper.*;
import com.xlh.dao.user.helper.UserClassMapperHelper;
import com.xlh.dto.conclude.ConcludeCheckDTO;
import com.xlh.dto.conclude.BusiFullMarkDTO;
import com.xlh.dto.conclude.ConcludeScoreDTO;
import com.xlh.exception.common.GlobalException;
import com.xlh.pojo.conclude.*;
import com.xlh.pojo.user.UserClass;
import com.xlh.service.conclude.ConcludeScoreService;
import com.xlh.util.BeanUtil;
import com.xlh.util.CellStyleUtils;
import com.xlh.util.StatisticExcelUtil;
import com.xlh.vo.conclude.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author cfz
 */
@Service
public class ConcludeScoreServiceImpl implements ConcludeScoreService {


    @Autowired
    private ConcludeStudentMapperHelper concludeStudentMapperHelper;

    @Autowired
    private ConcludeStudentMapperExt concludeStudentMapperExt;

    @Autowired
    private ConcludeCheckMapperHelper concludeCheckMapperHelper;

    @Autowired
    private ConcludeCheckSubMapperHelper concludeCheckSubMapperHelper;

    @Autowired
    private ConcludeCheckSubMapperExt concludeCheckSubMapperExt;

    @Autowired
    private ConcludeScoreMapperHelper concludeScoreMapperHelper;

    @Autowired
    private ConcludeScoreMapperExt concludeScoreMapperExt;

    @Autowired
    private ConcludeCourseMapperHelper concludeCourseMapperHelper;

    @Autowired
    private ConcludeMapperHelper concludeMapperHelper;

    @Autowired
    private ConcludeSubCourseMapperHelper concludeSubCourseMapperHelper;

    @Autowired
    private ConcludeSubCourseMapperExt concludeSubCourseMapperExt;

    @Autowired
    private ConcludeServiceImpl concludeServiceImpl;

    @Autowired
    private UserClassMapperHelper userClassMapperHelper;

    public static TimedCache<String, Object> timeChache = CacheUtil.newTimedCache(60000L);

    /**
     * excel 考核分项KEY值
     */
    private BusiFullMarkDTO scoreDTO;


    /**
     * 填充学生 和 考核分项信息
     * @param concludeId
     */
    @Override
    public void downLoadTemplate(Long concludeId, HttpServletRequest request, HttpServletResponse response) {

        if(ObjectUtil.isNull(concludeId)){
            throw new GlobalException(ResultEnum.NOT_FOUND_CONCLUDE);
        }
        //通过工具类创建writer
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        List<ConcludeCheck> checks = concludeCheckMapperHelper.findByCondition(concludeId);
        // 考核项
        if(CollectionUtil.isEmpty(checks)){
            throw new GlobalException(ResultEnum.NOT_FOUND_CHECK);
        }
        List<Long> checkIds = checks.stream().map(ConcludeCheck::getId).collect(Collectors.toList());
        List<ConcludeCheckSub> subInfos = concludeCheckSubMapperHelper.findByCheckIds(checkIds);
        // 考核分项
        if(CollectionUtil.isEmpty(subInfos)){
            throw new GlobalException(ResultEnum.NOT_FOUND_CHECK_SUB);
        }
        int countColumn = ConcludeContant.SCORE_BASE_COLUM.size() + (subInfos.size() * 2 );
        // 将check对象和sub对象进行合并
        List<ConcludeCheckDTO> concludeCheckDTOS = concludeServiceImpl.mergeCheckDtos(checks, subInfos);
        // 创建公共的注意事项头信息
        CellStyle announcementsStyle = commonHeaderCellStyle(workbook);
        CellStyle headerCellStyle = createHeaderCellStyle(workbook);
        CellStyle cellStyle = createCellStyle(workbook);

        createHeaderInfo(sheet, countColumn, concludeCheckDTOS, announcementsStyle, headerCellStyle);
        // 对subInfo重排序
        List<ConcludeCheckSub> concludeCheckSubs = Lists.newArrayList();
        for (ConcludeCheckDTO concludeCheckDTO : concludeCheckDTOS) {
            List<ConcludeCheckSub> concludeCheckSub = concludeCheckDTO.getConcludeCheckSubs();
            concludeCheckSubs.addAll(concludeCheckSub);
        }
        // 加载学生信息及分数
        List<ConcludeStudent> students = concludeStudentMapperHelper.findByCondition(concludeId, null);
        // 学生分项成绩和信息
        List<List<String>> studentScoreInfo = getStudentScoreInfo(concludeCheckSubs, students);
        // 加载数据信息
        loadDataToSheet(sheet, 3,countColumn, studentScoreInfo, cellStyle);
        // 设置列宽
        setDefaultWidth(sheet, countColumn);
        try{
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            StatisticExcelUtil.ExcelOutputStream("downLoadSubScoreTemplate", request, response, os);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 创建头信息
     * @param sheet
     * @param countColumn
     * @param concludeCheckDTOS
     * @param announcementsStyle
     * @param headerCellStyle
     */
    private void createHeaderInfo(HSSFSheet sheet, int countColumn, List<ConcludeCheckDTO> concludeCheckDTOS, CellStyle announcementsStyle, CellStyle headerCellStyle) {
        createCommonHeader(sheet, countColumn, announcementsStyle);
        // 创建考核分项头信息
        HSSFRow row1 = initCell(sheet, 1, countColumn, headerCellStyle);
        row1.setHeight((short) 600);
        HSSFRow row2 = initCell(sheet, 2, countColumn, headerCellStyle);
        row1.setHeight((short) 600);

        int columnIndex = ConcludeContant.SCORE_BASE_COLUM.size();
        for (int i = 0; i < ConcludeContant.SCORE_BASE_COLUM.size() + concludeCheckDTOS.size(); i++) {
            // 加载系统预制列
            if( i<=2 ){
                CellUtil.mergingCells(sheet, 1, 2, i, i, headerCellStyle);
                row1.getCell(i).setCellValue(ConcludeContant.SCORE_BASE_COLUM.get(i));
            }
            // 加载后续列信息
            if(i>2){
                // 第一行数据填充
                ConcludeCheckDTO concludeCheckDTO = concludeCheckDTOS.get(i - ConcludeContant.SCORE_BASE_COLUM.size());
                ConcludeCheck concludeCheck = concludeCheckDTO.getConcludeCheck();
                List<ConcludeCheckSub> concludeCheckSubs = concludeCheckDTO.getConcludeCheckSubs();
                CellUtil.mergingCells(sheet, 1, 1, columnIndex, columnIndex + (concludeCheckSubs.size() * 2) -1 , headerCellStyle);
                row1.getCell(columnIndex).setCellValue(concludeCheck.getName());
                // 第二行数据填充
                int baseIndex = 0;
                for (int j = 0; j < concludeCheckSubs.size(); j++) {
                    String subName = concludeCheckSubs.get(j).getName();
                    BigDecimal fullMark = concludeCheckSubs.get(j).getFullMark();

                    Console.log("==========第二行列信息输出=========" +   (columnIndex + baseIndex) + "====columnIndex====" + columnIndex);
                    row2.getCell(columnIndex + baseIndex).setCellValue(subName);
                    baseIndex++;
                    if(ObjectUtil.isNull(fullMark)){
                        Console.log("==========第二行列信息输出=========" +   (columnIndex + baseIndex) + "====columnIndex====" + columnIndex);
                        row2.getCell(columnIndex + baseIndex ).setCellValue("满分："+ ConcludeContant.DEFAULT_SCORE);
                    }else{
                        row2.getCell(columnIndex + baseIndex ).setCellValue("满分："+ fullMark.intValue());
                    }
                    baseIndex++;
                }
                columnIndex = columnIndex + (concludeCheckSubs.size() * 2);
            }
        }
    }


    /**
     * 普通cell的样式创建
     * @param workbook
     * @return
     */
    private CellStyle createCellStyle(HSSFWorkbook workbook) {

        CellStyle headerCellStyle = workbook.createCellStyle();
        CellStyleUtils.addFullBorder(headerCellStyle);

        return headerCellStyle;
    }


    /**
     * 创建头样式
     * @param workbook
     * @return
     */
    private CellStyle createHeaderCellStyle(HSSFWorkbook workbook) {

        CellStyle headerCellStyle = workbook.createCellStyle();

        CellStyleUtils.addFullBorder(headerCellStyle);
        // 设置背景色为蓝色
        HSSFPalette palette = workbook.getCustomPalette();
        palette.setColorAtIndex((short) 12,(byte) 141, (byte) 180, (byte) 226);  // 深蓝

        //solid 填充  foreground  前景色
        headerCellStyle.setFillForegroundColor(palette.getColor((short) 12).getIndex());
        headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        return headerCellStyle;
    }

    /**
     * 创建第二行的样式
     * @param workbook
     * @return
     */
    private CellStyle creadeSecondHeaderStyle(HSSFWorkbook workbook){

        CellStyle headerCellStyle = workbook.createCellStyle();
        CellStyleUtils.addFullBorder(headerCellStyle);

        HSSFPalette palette = workbook.getCustomPalette();
        palette.setColorAtIndex((short) 11,(byte) 197, (byte) 217, (byte) 241);  // 浅蓝色
        //solid 填充  foreground  前景色
        headerCellStyle.setFillForegroundColor(palette.getColor((short) 11).getIndex());
        headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        return headerCellStyle;
    }



    /**
     * 公共头部样式
     * @param workbook
     * @return
     */
    private CellStyle commonHeaderCellStyle(HSSFWorkbook workbook) {

        CellStyle headerCellStyle = workbook.createCellStyle();
        HSSFFont redFont = workbook.createFont();
        redFont.setColor(Font.COLOR_RED);
        headerCellStyle.setFont(redFont);

        CellStyleUtils.addFullBorder(headerCellStyle);

        return headerCellStyle;
    }


    /**
     * 考核分项模板下载
     * @param concludeId
     * @param checkId
     */
    @Override
    public void downTemplateSub(Long concludeId, Long checkId, HttpServletRequest request, HttpServletResponse response) {

        // 创建excel列信息
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        // 创建公共的注意事项头信息
        CellStyle announcementsStyle = commonHeaderCellStyle(workbook);
        CellStyle headerCellStyle = createHeaderCellStyle(workbook);
        CellStyle cellStyle = createCellStyle(workbook);

        List<ConcludeCheckSub> subInfos = concludeCheckSubMapperHelper.findByCheckIds(Lists.newArrayList(checkId));
        if(CollectionUtil.isEmpty(subInfos)){
            throw new GlobalException(ResultEnum.NOT_FOUND_CHECK_SUB);
        }
        int countColumn = ConcludeContant.SCORE_BASE_COLUM.size() + (subInfos.size() * 2 );
        // 创建公共的注意事项头信息
        createCommonHeader(sheet, countColumn, announcementsStyle);
        createSubHeader(sheet, headerCellStyle, subInfos, countColumn);
        // 加载学生信息及分数
        List<ConcludeStudent> students = concludeStudentMapperHelper.findByCondition(concludeId, null);
        // 学生分项成绩和信息
        List<List<String>> studentScoreInfo = getStudentScoreInfo(subInfos, students);
        // 加载数据信息
        loadDataToSheet(sheet, 2,countColumn, studentScoreInfo, cellStyle);
        // 设置列宽
        setDefaultWidth(sheet, countColumn);
        try{
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            StatisticExcelUtil.ExcelOutputStream("downLoadSubScoreTemplate", request, response, os);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 创建考核项的头部信息
     * @param sheet
     * @param headerCellStyle
     * @param subInfos
     * @param countColumn
     */
    private void createSubHeader(HSSFSheet sheet, CellStyle headerCellStyle, List<ConcludeCheckSub> subInfos, int countColumn) {

        Console.log("  countColumn " + countColumn);
        Console.log("  subInfos.size() " + subInfos.size());

        // 创建考核分项头信息
        HSSFRow row = initCell(sheet, 1, countColumn, headerCellStyle);
        row.setHeight((short) 400);
        for (int i = 0; i < countColumn; i++) {
            // 加载系统预制列
            if(i<=2){
                row.getCell(i).setCellValue(ConcludeContant.SCORE_BASE_COLUM.get(i));
            }

        }
        int baseRow = ConcludeContant.SCORE_BASE_COLUM.size();
        // 增加后序列的追加
        for (int i = 0; i < subInfos.size(); i++) {

            ConcludeCheckSub concludeCheckSub = subInfos.get(i);
            row.getCell(baseRow).setCellValue(concludeCheckSub.getName());
            baseRow ++;

            if(ObjectUtil.isNotNull(concludeCheckSub.getFullMark())){
                row.getCell(baseRow).setCellValue("满分：" + concludeCheckSub.getFullMark().intValue());
            }else{
                row.getCell(baseRow).setCellValue("满分：" + ConcludeContant.DEFAULT_SCORE);
            }
            baseRow ++;
        }

    }


    /**
     * 写入数据到sheet页中
     * @param sheet
     * @param countColumn
     * @param studentScoreInfo
     */
    private void loadDataToSheet(HSSFSheet sheet, int startIndex ,int countColumn, List<List<String>> studentScoreInfo, CellStyle cellStyle) {
        for (int i = 0; i < studentScoreInfo.size(); i++) {

            List<String> rowInfo = studentScoreInfo.get(i);
            int rowIndex = startIndex + i;
            HSSFRow row = initCell(sheet, rowIndex, countColumn, cellStyle);
            int columnIndex = 3;
            for (int j = 0; j < rowInfo.size(); j++) {

                if(j<=2){
                    row.getCell(j).setCellValue(rowInfo.get(j));
                }
                // 加载后续列信息
                if(j>2){
                    CellUtil.mergingCells(sheet, rowIndex,  rowIndex, columnIndex, columnIndex + 1, cellStyle);
                    row.getCell(columnIndex).setCellValue(rowInfo.get(j));
                    columnIndex = columnIndex + 2;
                }

            }
        }
    }


    /**
     * 获取所有的学生信息和关联的考核分项成绩
     * @param subInfos
     * @param students
     */
    private List<List<String>> getStudentScoreInfo(List<ConcludeCheckSub> subInfos,
                                                   List<ConcludeStudent> students) {
        List<List<String>> result = Lists.newArrayList();
        for (int i = 0; i < students.size(); i++) {

            ConcludeStudent student = students.get(i);
            List<String> list = Lists.newArrayList();
            list.add(String.valueOf(i+1)); // 序号
            list.add(student.getName()); // 学生姓名
            list.add(student.getSno()); // 学生学号
            for (ConcludeCheckSub subInfo : subInfos) {
                ConcludeScore studentSubScore = concludeScoreMapperHelper.findCheckSubScoreBySubIdAndSno(subInfo.getId(), student.getSno());
                if(ObjectUtils.isEmpty(studentSubScore)){
                    list.add(""); // 学生学号
                }else{
                    list.add(studentSubScore.getScore().stripTrailingZeros().toPlainString()); // 学生学号
                }
            }
            result.add(list);
        }
        return result;
    }


    /**
     * 创建公共的注意事项的头信息
     * @param sheet
     */
    private HSSFRow createCommonHeader(HSSFSheet sheet, int countColumn, CellStyle cellStyle) {

        HSSFRow row = initCell(sheet, 0, countColumn, cellStyle);
        CellUtil.mergingCells( sheet,0,0,0, countColumn - 1, null );
        row.getCell(0).setCellValue( ConcludeContant.ANNOUNCEMENTS );
        row.getCell(0).setCellStyle(cellStyle);
        row.setHeight((short) 600);
        return row;
    }

    /**
     * 初始化指定行
     * @param sheet
     * @param rowIndex
     * @param countColumn
     * @return
     */
    private HSSFRow initCell(HSSFSheet sheet, int rowIndex,int countColumn, CellStyle cellStyle) {
        HSSFRow row = sheet.createRow(rowIndex);
        for (int i = 0; i < countColumn; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
        }
        return row;
    }

    /**
     * 设置默认的列宽
     * @param sheet
     * @param countColum
     */
    private void setDefaultWidth(HSSFSheet sheet, int countColum) {
        for (int i = 0; i < countColum; i++) {
            sheet.setColumnWidth(i, 256*13+184);
        }
    }

    /**
     * 执行插入数据操作
     * @param checkId
     * @param headerContent
     * @param data
     * @return
     */
    public Integer executeInsertScore(Long checkId, List<Object> headerContent, List<List<Object>> data) {

        // 获取所有考核分项名称
        List<ConcludeCheckSub> subInfo = concludeCheckSubMapperHelper.findByCheckIds(Lists.newArrayList(checkId));
        // 获取当前所有学生
        List<ConcludeScore> insertRecords = Lists.newArrayList();
        // 更新考核分项分数
        List<ConcludeCheckSub> subFullScoreUpRecords = Lists.newArrayList();

        // 执行头满分的更新
        for (int i = 3; i < headerContent.size(); i++) {

            String subName = headerContent.get(i).toString();
            Optional<ConcludeCheckSub> subRecord = subInfo.stream().filter(info -> info.getName().equals(subName)).findFirst();
            String fullMark = headerContent.get(++i).toString().replaceAll(ConcludeContant.FULL_SCORE_TIP,"").trim();
            Console.log("++++++++"+fullMark+"++++++++++++");
            subRecord.ifPresent(record -> {
                ConcludeCheckSub subFullScoreUpRecord = new ConcludeCheckSub();
                subFullScoreUpRecord.setId(record.getId());
                subFullScoreUpRecord.setFullMark(new BigDecimal(fullMark));
                subFullScoreUpRecords.add(subFullScoreUpRecord);
            });
        }

        // 插入sub项的
        if(CollectionUtil.isNotEmpty(subFullScoreUpRecords)){
            concludeCheckSubMapperHelper.updateBatch(subFullScoreUpRecords);
        }


        // 执行数据更新
        int counter = 0;

        for (int i = 0; i <data.size(); i++) {
            List<Object> objects = data.get(i);
            // 校验当前记录是否通过
            counter++;
            for (int j = 3; j < objects.size(); j++) {

                if(j + 1 <= headerContent.size()-1){
                    ConcludeScore concludeScore = new ConcludeScore();

                    String subName = headerContent.get(j).toString();
                    concludeScore.setStudentNo(objects.get(2).toString());
                    concludeScore.setCheckId(checkId);
                    Optional<ConcludeCheckSub> subRecord = subInfo.stream().filter(info -> info.getName().equals(subName)).findFirst();

                    if(StrUtil.isNotBlank(objects.get(j).toString())){
                        concludeScore.setScore(NumberUtil.round(objects.get(j).toString(), 2));
                        subRecord.ifPresent(checkSub -> {
                            concludeScore.setCheckSubId(checkSub.getId());
                            insertRecords.add(concludeScore);
                        });
                    }
                }
            }
        }

        // 执行删除
        if(CollectionUtil.isNotEmpty(insertRecords)){
            concludeScoreMapperHelper.deleteBySubIdAndSno(insertRecords);
        }
        // 批量插入结果
        if(CollectionUtil.isNotEmpty(insertRecords)){
            concludeScoreMapperExt.insertBatch(insertRecords);
        }

        return counter;
    }

    /**
     * 按照考核分项导入成绩
     * @param checkId
     * @param file
     * @param headerIndex
     * @return
     */
    @Override
    public Map<String, Object> updateScoreSub(Long checkId, MultipartFile file, int headerIndex) {

        Map<String, Object> resultMessage = new HashMap<>();
        // 文件校验
        fileCheck(file);
        // 1.获取上传文件输入流
        InputStream inputStream = null;
        try{
            inputStream = file.getInputStream();
        }catch (Exception e){
            throw new GlobalException(ResultEnum.ACCOUNT_EXPIRED);
        }
        // ExcelUtil获取ExcelReader指定输入流和sheet
        ExcelReader excelReader = ExcelUtil.getReader(inputStream, 0, true);
        // 获取header信息
        List<List<Object>> headerInfo = excelReader.read(0, 1);
        // 校验头信息是否正确
        subHeaderCheck(checkId, headerInfo);
        // 校验数据区信息是否正确
        // 插入数据
        ConcludeCheck check = concludeCheckMapperHelper.getById(checkId);
        List<ConcludeStudent> countStudent = concludeStudentMapperHelper.findByCondition( check.getConcludeId(), null );
        List<String> snos = countStudent.stream().map(ConcludeStudent::getSno).collect(Collectors.toList());
        List<List<Object>> dataInfo = excelReader.read(2);
        List<ExcelImportTipVO> excelImportTipVOS = entiretyDataCheck(dataInfo, headerInfo.get(headerInfo.size() - 1), snos, 3);
        if(CollectionUtil.isNotEmpty(excelImportTipVOS)){
            resultMessage.put("ERROR", excelImportTipVOS);
            return resultMessage;
        }
        List<Integer> studentRecord = Lists.newArrayList();
        Integer insertRecord = executeInsertScore(check.getId(), headerInfo.get( headerInfo.size() - 1 ), dataInfo);
        studentRecord.add(insertRecord);
        // 校验学生信息是否正常
        Integer insertMaxRecord = studentRecord.stream().max(NumberUtil::compare).get();
        resultMessage.put("SUCCESS", insertMaxRecord+"/"+countStudent.size());
        return  resultMessage;
    }

    /**
     * 检查上传考核分项的头信息和文件的头信息是否相同
     * @param checkId 考核项ID
     * @param headerContent 头信息
     */
    private void subHeaderCheck(Long checkId, List<List<Object>> headerContent) {

        StringBuffer headerInfoBuffer = getUploadStrBuffer(headerContent);
        // 创建excel列信息
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        // 创建公共的注意事项头信息
        CellStyle announcementsStyle = commonHeaderCellStyle(workbook);
        CellStyle headerCellStyle = createHeaderCellStyle(workbook);

        List<ConcludeCheckSub> subInfos = concludeCheckSubMapperHelper.findByCheckIds(Lists.newArrayList(checkId));
        int countColumn = ConcludeContant.SCORE_BASE_COLUM.size() + (subInfos.size() * 2 );
        // 创建公共的注意事项头信息
        createCommonHeader(sheet, countColumn, announcementsStyle);
        createSubHeader(sheet, headerCellStyle, subInfos, countColumn);

        StringBuffer templateHeaderInfo = new StringBuffer();
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i <= lastRowNum; i++) {
            HSSFRow row = sheet.getRow(i);
            short lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                String cellStr = row.getCell(j).getStringCellValue().trim();
                if(!cellStr.contains(ConcludeContant.FULL_SCORE_TIP)){
                    templateHeaderInfo.append(cellStr);
                }else{
                    // 检查考核分项
                    Integer maxFullScore = ConcludeContant.MAX_FULL_SCORE;
                    String fullMarkStr = cellStr.replace(ConcludeContant.FULL_SCORE_TIP, "").trim();
                    boolean isInteger = NumberUtil.isInteger(fullMarkStr);
                    if(isInteger == false){
                        throw new GlobalException(ResultEnum.ERROR_FULL_MARK_NOT_NUMBER);
                    }
                    Integer fullMarkInt = Integer.valueOf(fullMarkStr);
                    if(fullMarkInt > maxFullScore || fullMarkInt < 0 ){
                        throw new GlobalException(ResultEnum.ERROR_MUST_LESS_FULL_MARK);
                    }
                }

            }
        }
        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

        // 头信息比对
        if(!headerInfoBuffer.toString().equals(templateHeaderInfo.toString())){
            throw new GlobalException(ResultEnum.NOT_FOUND_CHECK);
        }

    }

    /**
     * 获取拼装头信息的stringBuffer
     * @param headerContent
     * @return
     */
    private StringBuffer getUploadStrBuffer(List<List<Object>> headerContent) {

        StringBuffer headerInfoBuffer = new StringBuffer();

        for (int i = 0; i < headerContent.size(); i++) {
            List<Object> rowInfo = headerContent.get(i);
            for (int j = 0; j < rowInfo.size(); j++) {
                // 过滤满分项
                Object object = rowInfo.get(j);
                // 验证最后一行不能为空
                if (i == (headerContent.size() - 1) && j > 2) {
                    if (object == null || StrUtil.isBlank(object.toString())) {
                        throw new GlobalException(ResultEnum.NOT_MATCH_TEMPLATE);
                    }
                }
                if (!object.toString().trim().contains(ConcludeContant.FULL_SCORE_TIP)) {
                    headerInfoBuffer.append(object.toString().trim());
                }
            }
        }
        return headerInfoBuffer;
    }

    /**
     * 整体数据导入
     * @param concludeId
     * @param file
     */
    @Override
    public Map<String, Object> updateScoreEntirety(Long concludeId,  MultipartFile file) {


        Map<String, Object> resultMessage = new HashMap<>();
        // 文件校验
        fileCheck(file);
        // 1.获取上传文件输入流
        InputStream inputStream = null;
        try{
            inputStream = file.getInputStream();
        }catch (Exception e){
            throw new GlobalException(ResultEnum.ACCOUNT_EXPIRED);
        }
        // ExcelUtil获取ExcelReader指定输入流和sheet
        ExcelReader excelReader = ExcelUtil.getReader(inputStream, 0, true);
        // 获取header信息
        List<List<Object>> headerInfo = excelReader.read(0, 2);
        // 校验头信息是否正确
        entiretyHeaderCheck(concludeId, headerInfo);
        // 校验数据区信息是否正确
        List<ConcludeStudent> countStudent = concludeStudentMapperHelper.findByCondition(concludeId, null);
        List<String> snos = countStudent.stream().map(ConcludeStudent::getSno).collect(Collectors.toList());
        List<List<Object>> dataInfo = excelReader.read(3);
        List<ExcelImportTipVO> tipInfos = entiretyDataCheck(dataInfo, headerInfo.get(headerInfo.size() - 1), snos, 4);

        if(CollectionUtil.isNotEmpty(tipInfos)){
            resultMessage.put(ConcludeContant.ERROR_FLAGE, tipInfos);
            return resultMessage;
        }

        // 插入数据
        List<ConcludeCheck> checks = concludeCheckMapperHelper.findByCondition(concludeId);

        Integer insertRecord = executeEntiretyInsertScore(checks, headerInfo, dataInfo);

        resultMessage.put(ConcludeContant.SUCCESS_FLAGE, insertRecord+"/"+countStudent.size());

        return  resultMessage;
    }

    /**
     * 执行整体导入操作
     * @param checks
     * @param headerInfo
     * @param dataInfo
     * @return
     */
    private Integer executeEntiretyInsertScore(List<ConcludeCheck> checks, List<List<Object>> headerInfo, List<List<Object>> dataInfo) {

        // 1. 执行插入考核分项分数
        int columSize = headerInfo.get(0).size();
        List<Object> checkInfo = headerInfo.get(1);
        List<Object> subCheckInfo = headerInfo.get(2);
        List<ConcludeCheckSub> subFullScoreUpRecords = Lists.newArrayList();

        Map<Integer, ConcludeCheckSub> columnMap = new HashMap<>();
        String checkName = "";
        for (int i = 3; i < columSize; i++) {

            // 考核项列
            if(StrUtil.isNotBlank(checkInfo.get(i).toString())){
                checkName = checkInfo.get(i).toString();
            }
            String finalCheckName = checkName;
            Optional<ConcludeCheck> optionCheck = checks.stream().filter(check -> check.getName().equals(finalCheckName)).findFirst();
            String subName = subCheckInfo.get(i).toString();
            String fullMark = subCheckInfo.get(++i).toString().replaceAll(ConcludeContant.FULL_SCORE_TIP,"").trim();

            if(optionCheck.isPresent()){

                ConcludeCheck check = optionCheck.get();
                Long checkId = check.getId();
                // 查询下面的考核分项
                List<ConcludeCheckSub> subRecords = concludeCheckSubMapperHelper.findByCheckIds(Lists.newArrayList(checkId));
                // 考核分项信息
                Optional<ConcludeCheckSub> optionCheckSub = subRecords.stream().filter(checkSub -> checkSub.getName().equals(subName)).findFirst();
                optionCheckSub.ifPresent(record -> {
                    ConcludeCheckSub subFullScoreUpRecord = new ConcludeCheckSub();
                    subFullScoreUpRecord.setId(record.getId());
                    subFullScoreUpRecord.setFullMark(new BigDecimal(fullMark));
                    subFullScoreUpRecords.add(subFullScoreUpRecord);
                });

                if(optionCheckSub.isPresent()){
                    columnMap.put(i-1, optionCheckSub.get());
                }
            }
        }

        if(CollectionUtil.isNotEmpty(subFullScoreUpRecords)){
            concludeCheckSubMapperHelper.updateBatch(subFullScoreUpRecords);
        }

        List<ConcludeScore> insertRecords = Lists.newArrayList();
        // 插入数据
        for (int i = 0; i <dataInfo.size(); i++) {
            List<Object> objects = dataInfo.get(i);
            // 校验当前记录是否通过
            for (int j = 3; j < objects.size(); j++) {
                // 没填写答案的情况
                if(StrUtil.isBlank(objects.get(j).toString())){
                    j++;
                    continue;
                }
                ConcludeCheckSub concludeCheckSub = columnMap.get(j);
                ConcludeScore concludeScore = new ConcludeScore();
                concludeScore.setStudentNo(objects.get(2).toString());
                concludeScore.setCheckId(concludeCheckSub.getCheckId());
                concludeScore.setCheckSubId(concludeCheckSub.getId());
                // 设置两位小数
                concludeScore.setScore(NumberUtil.round(objects.get(j).toString(),2));
                insertRecords.add(concludeScore);
                j++;
            }
        }
        // 执行删除
        if(CollectionUtil.isNotEmpty(insertRecords)){
            concludeScoreMapperHelper.deleteBySubIdAndSno(insertRecords);
        }
        if(CollectionUtil.isNotEmpty(insertRecords)){
            concludeScoreMapperExt.insertBatch(insertRecords);
        }
        return dataInfo.size();
    }


    /**
     * 校验数据的合法性
     * @param dataInfo
     * @param headerRowInfo
     * @return
     */
    private List<ExcelImportTipVO> entiretyDataCheck(List<List<Object>> dataInfo,
                                                     List<Object> headerRowInfo,
                                                     List<String> snos, int baseLine) {

        // 行信息
        List<Map<String, String>> errorInfoList = Lists.newArrayList();

        List<String> insertSonRecord = Lists.newArrayList();

        for (int i = 0; i < dataInfo.size(); i++) {

            List<Object> rowInfo = dataInfo.get(i);
            Object index = rowInfo.get(0);
            Object studentName = rowInfo.get(1);
            Object studentNo = rowInfo.get(2);

            String errorLine = String.valueOf(i + baseLine); // 行信息
            if(ObjectUtil.isNull(index) || StrUtil.isBlank(index.toString())){
                addInfoToErrorList(errorInfoList, "序号不能为空",errorLine);
            }
            if(ObjectUtil.isNotNull(index) && StrUtil.isNotBlank(index.toString())){
                if(!NumberUtil.isInteger(index.toString())){
                    addInfoToErrorList(errorInfoList, "序号必须为数字",errorLine);
                }
                if(NumberUtil.isInteger(index.toString())){
                    if(!index.toString().trim().equals(String.valueOf((i+1)))){
                        addInfoToErrorList(errorInfoList, "序号需按顺序排列",errorLine);
                    }
                }
            }
            // 校验学生姓名
            if(ObjectUtil.isNull(studentName) || StrUtil.isBlank(studentName.toString())){
                addInfoToErrorList(errorInfoList, "学生姓名不能为空",errorLine);
            }
            // 校验学生学号
            if(ObjectUtil.isNull(studentNo) || StrUtil.isBlank(studentNo.toString())){
                addInfoToErrorList(errorInfoList, "学号不能为空",errorLine);
            }
            if(ObjectUtil.isNotNull(studentNo) && StrUtil.isNotBlank(studentNo.toString())){
                if(!studentNo.toString().matches(ConcludeContant.STUNO_REGEX)){
                    addInfoToErrorList(errorInfoList, "学号必须由数字或字母组成",errorLine);
                }
                // 学号未删除当前学生库的学号
                if(!snos.contains(studentNo.toString())){
                    addInfoToErrorList(errorInfoList, "未查询到当前学号："+studentNo, errorLine);
                }else{
                    if(insertSonRecord.contains(studentNo.toString()) == false){
                        insertSonRecord.add(studentNo.toString());
                    }else{
                        addInfoToErrorList(errorInfoList, "存在重复学号："+studentNo, errorLine);
                    }
                }
            }

            if(rowInfo.size() != headerRowInfo.size()){
                addInfoToErrorList(errorInfoList, "行信息不合法， 超出表头最大列",errorLine);
                continue;
            }

            List<Object> objects = rowInfo.subList(3, rowInfo.size());

            boolean match = true;

            for (int j = 0; j <objects.size(); j++) {

                if( objects.get(j) == null ){
                    continue;
                }
                String columStr = objects.get(j).toString();
                if(j%2 == 1){
                   if(StrUtil.isNotBlank(columStr)){
                       match = false;
                       break;
                   }
                }
            }
            // 创建 Pattern 对象
            if(match == false){
                addInfoToErrorList( errorInfoList, "行信息不合法， 行格式不正确", errorLine);
                continue;
            }
            // 分数校验
            for (int j = 3; j < rowInfo.size(); j++) {

                Object score = rowInfo.get(j);
                // 校验学生姓名
                if(ObjectUtil.isNotNull( score )){
                    if(StrUtil.isNotBlank(score.toString())){
                        // 如果不为分数或者正整数
                        if(!score.toString().matches("^[+]{0,1}(\\d+)$") // 正整数
                                || !score.toString().matches("^[0-9]+.?[0-9]*$") ){ // 小数
                            addInfoToErrorList(errorInfoList, "第"+(j + 1) +"列" + "分数不合法",errorLine);
                        }else{
                            BigDecimal scoreBig = new BigDecimal(score.toString());
                            BigDecimal fullMark = new BigDecimal(headerRowInfo.get(j + 1).toString().replace(ConcludeContant.FULL_SCORE_TIP, "").trim());
                            if(scoreBig.compareTo(fullMark) == 1){
                                addInfoToErrorList(errorInfoList, "第"+(j + 1 )+"列" + "分数不能超过满分",errorLine);
                            }
                        }
                    }
                }
            }
        }

        Map<String, List<String>> stringListMap = MapUtil.toListMap(errorInfoList);
        Set<String> errorInfos = stringListMap.keySet();
        List<ExcelImportTipVO> convertResult = Lists.newArrayList();
        for (String errorInfo : errorInfos) {

            ExcelImportTipVO excelImportTipVO = new ExcelImportTipVO();
            excelImportTipVO.setErrorInfo(errorInfo);
            List<String> errorLines = stringListMap.get(errorInfo);
            excelImportTipVO.setErrorLine(errorLines);
            convertResult.add(excelImportTipVO);
        }

        return convertResult;
    }

    /**
     * 将错误信息填写到List中
     * @param errorInfoList 收集错误信息map
     * @param errorInfo 错误信息
     * @param errorLine 错误信息行
     */
    private void addInfoToErrorList(List<Map<String, String>> errorInfoList, String errorInfo, String errorLine) {
        Map<String, String> errorInfoMap = new HashMap<>();
        errorInfoMap.put(errorInfo, errorLine);
        errorInfoList.add(errorInfoMap);
    }

    /**
     * 整体导入文件头校验
     * @param concludeId 目标ID
     * @param headerContent 头信息
     */
    private void entiretyHeaderCheck(Long concludeId, List<List<Object>> headerContent) {

        StringBuffer headerInfoBuffer = getUploadStrBuffer(headerContent);

        // excel头文件信息
        String headerInfo = headerInfoBuffer.toString();
        //通过工具类创建writer
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        List<ConcludeCheck> checks = concludeCheckMapperHelper.findByCondition(concludeId);
        List<Long> checkIds = checks.stream().map(ConcludeCheck::getId).collect(Collectors.toList());
        List<ConcludeCheckSub> subInfos = concludeCheckSubMapperHelper.findByCheckIds(checkIds);
        int countColumn = ConcludeContant.SCORE_BASE_COLUM.size() + (subInfos.size() * 2 );
        // 将check对象和sub对象进行合并
        List<ConcludeCheckDTO> concludeCheckDTOS = concludeServiceImpl.mergeCheckDtos(checks, subInfos);
        // 创建公共的注意事项头信息
        CellStyle announcementsStyle = commonHeaderCellStyle(workbook);
        CellStyle headerCellStyle = createHeaderCellStyle(workbook);
        createHeaderInfo(sheet, countColumn, concludeCheckDTOS, announcementsStyle, headerCellStyle);
        StringBuffer templateHeaderInfo = new StringBuffer();
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i <= lastRowNum; i++) {
            HSSFRow row = sheet.getRow(i);
            short lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                HSSFCell cell = row.getCell(j);
                String cellValue = cell.getStringCellValue().trim();
                // 过滤满分项
                if(!cellValue.trim().contains(ConcludeContant.FULL_SCORE_TIP)){
                    templateHeaderInfo.append(cellValue.trim()) ;
                }else{
                    // 检查考核分项
                    Integer maxFullScore = ConcludeContant.MAX_FULL_SCORE;
                    String fullMarkStr = cellValue.replace(ConcludeContant.FULL_SCORE_TIP, "").trim();
                    boolean isInteger = NumberUtil.isInteger(fullMarkStr);
                    if(isInteger == false){
                        throw new GlobalException(ResultEnum.ERROR_FULL_MARK_NOT_NUMBER);
                    }
                    Integer fullMarkInt = Integer.valueOf(fullMarkStr);
                    if(fullMarkInt > maxFullScore || fullMarkInt < 0 ){
                        throw new GlobalException(ResultEnum.ERROR_MUST_LESS_FULL_MARK);
                    }
                }
            }
        }
        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

        // 头信息比对
        if(!headerInfo.equals(templateHeaderInfo.toString())){
            throw new GlobalException(ResultEnum.NOT_MATCH_TEMPLATE);
        }
    }

    /**
     * 文件类型校验
     * @param file
     */
    private void fileCheck(MultipartFile file) {
        if(ObjectUtil.isNull(file)){
            throw new GlobalException(ResultEnum.FILE_UPLOAD_ERROR);
        }
        // 1. 文件名后缀扩展校验
        String extName = FileUtil.extName(file.getName());
        // 2. 文件头校验
        if(extName.endsWith(".xls")){
            throw new GlobalException(ResultEnum.ERROR_FILE_TYPE);
        }
    }

    @Override
    public List<ConcludeScoreCheckVO> statistics(Long concludeId) {

        // 无课程目标所有都不战输
        Conclude conclude = concludeMapperHelper.get(concludeId);
        if(ObjectUtil.isNull(conclude)){
            return null;
        }
        List<ConcludeCourse> concludeCourses = concludeCourseMapperHelper.findByCondition(concludeId);
        if(CollectionUtil.isEmpty(concludeCourses)){
            return null;
        }
        // 查询所有的考核项
        List<ConcludeCheck> concludeChecks = concludeCheckMapperHelper.findByConcludeId(concludeId);
        if(CollectionUtil.isEmpty(concludeChecks)){
            return null;
        }
        List<ConcludeScoreCheckVO> concludeScoreCheckVOS = Lists.newArrayList();
        // 考核项索引
        int index = 1;
        for (ConcludeCheck concludeCheck : concludeChecks) {

            // 考核项权重为空
            BigDecimal checkWeight = concludeCheck.getWeight();
            if(ObjectUtil.isNull(checkWeight)){
                continue;
            }
            // 考核项下无考核分项
            List<ConcludeCheckSub> concludeCheckSubs = concludeCheckSubMapperHelper.findByCheckIds(Lists.newArrayList(concludeCheck.getId()));
            if(CollectionUtil.isEmpty(concludeCheckSubs)){
                continue;
            }
            // 创建头信息
            ConcludeScoreCheckVO concludeScoreCheckVO = new ConcludeScoreCheckVO();
            concludeScoreCheckVO.setCheckName("考核项"+ index + ":  " + concludeCheck.getName());
            concludeScoreCheckVO.setFullMark(checkWeight.stripTrailingZeros().toPlainString());
            concludeScoreCheckVO.setCheckId(concludeCheck.getId());

            ConcludeScoreHeaderVO header = initHeader(concludeId);
            concludeScoreCheckVO.setHeader(header);
            // 查询考核项下的所有考核分项
            List<ConcludeScoreContentVO> content = getContent(concludeCheck, concludeCheckSubs, header.getCourseHeaderVO(), conclude);
            concludeScoreCheckVO.setContent(content);
            String gradeStr = content.get(content.size() - 1).getRowContent().get(2);
            if(StrUtil.isBlank(gradeStr)){
                concludeScoreCheckVO.setGrade(new BigDecimal("0"));
            }else{
                concludeScoreCheckVO.setGrade(new BigDecimal(gradeStr).stripTrailingZeros());
            }
            concludeScoreCheckVOS.add(concludeScoreCheckVO);

            index++;
        }
        // 未生成行下记录
        if(CollectionUtil.isEmpty(concludeScoreCheckVOS)){
            return null;
        }
        insertSummer(concludeScoreCheckVOS, conclude);

        return concludeScoreCheckVOS;
    }

    /**
     * 整体
     * @param concludeId
     */
    @Override
    public void entiretyDownload(Long concludeId, HttpServletRequest request, HttpServletResponse response) {

        // 整体数据下载
        List<ConcludeScoreCheckVO> excelData = statistics(concludeId);

        if(ObjectUtil.isNull(excelData)){
            throw new GlobalException(ResultEnum.NOT_FOUND_DATA_INFO);
        }
        //通过工具类创建writer
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建sheet页
        HSSFSheet sheet = workbook.createSheet();

        CellStyle cellStyle = createCellStyle(workbook);
        CellStyle firstHeaderStyle = CellStyleUtils.universalTitleStyle(workbook);

        // 设置头字体
        /*Font firstFont= workbook.createFont();
        firstFont.setFontHeightInPoints((short) 14);
        firstFont.setFontName("宋体");
        firstFont.setColor(IndexedColors.WHITE.index);
        firstHeaderStyle.setFont(firstFont);*/
        // 设置第二行字体
        CellStyle secondHeaderStyle = creadeSecondHeaderStyle(workbook);
        CellStyleUtils.addFullBorder(secondHeaderStyle);
        Font secondFont= workbook.createFont();
        secondFont.setFontHeightInPoints((short) 14);
        secondFont.setFontName("宋体");
        secondHeaderStyle.setFont(secondFont);

        // 创建行的指针
        int baseRow = 0;
        int columCount = excelData.get(0).getContent().get(0).getRowContent().size() + 1;
        for (int i = 0; i < excelData.size(); i++) {
            // 创建头文件
            HSSFRow row = sheet.createRow(baseRow);
            row.setHeight((short)800);
            //1. 循环考核项
            ConcludeScoreCheckVO concludeScoreCheckVO = excelData.get(i);
            //2. 表示行的长度
            int size = columCount;

            // 3. 表示列的长度
            for (int j = 0; j < size; j++) {
                HSSFCell cell = row.createCell(j);
                cell.setCellStyle(firstHeaderStyle);
            }
            // 对于第一行进行合并
            CellUtil.mergingCells(sheet,baseRow,baseRow,0,size-3, firstHeaderStyle);
            row.getCell(0).setCellValue(concludeScoreCheckVO.getCheckName());
            row.getCell(size-2).setCellValue("满分" + concludeScoreCheckVO.getFullMark());
            row.getCell(size-1).setCellValue("成绩" + concludeScoreCheckVO.getGrade().doubleValue());
            baseRow ++;
            // 填充头信息 第二行和第三行
            ConcludeScoreHeaderVO header = concludeScoreCheckVO.getHeader();
            // 创建两行
            for (int j = 0; j < 2 ; j++) {
                HSSFRow tempRow = sheet.createRow(j + baseRow);
                tempRow.setHeight((short) 400);
                for (int k = 0; k < size; k++) {
                    HSSFCell cell = tempRow.createCell(k);
                    cell.setCellStyle(secondHeaderStyle);
                }
            }
            // 进行合并
            CellUtil.mergingCells(sheet, baseRow, baseRow+1, 0, 0, firstHeaderStyle);
            sheet.getRow(baseRow).getCell(0).setCellValue(header.getSubHeaderName());
            sheet.getRow(baseRow).getCell(0).setCellStyle(secondHeaderStyle);
            HSSFRow row1 = sheet.getRow(baseRow);
            HSSFRow row2 = sheet.getRow(baseRow + 1);
            int index = 1;
            // 循环头
            for (int column = 0; column <header.getCourseHeaderVO().size(); column++) {
                // 从第一列开始

                if(header.getCourseHeaderVO().get(column).getConcludeCourseId() == -1l){
                    CellUtil.mergingCells(sheet, baseRow, baseRow, index, index+2, secondHeaderStyle);
                    row1.getCell(index).setCellValue(ConcludeContant.HEAR_ZERO);
                    row1.getCell(index).setCellStyle(secondHeaderStyle);

                    String[] split = ConcludeContant.SECOND_HEAR_ZERO_ROW1.split("/");
                    row2.getCell(index).setCellValue(split[0]);
                    row2.getCell(++index).setCellValue(split[1]);
                    row2.getCell(index+1).setCellValue(ConcludeContant.SECOND_HEAR_ZERO_ROW2);
                }else{
                    CellUtil.mergingCells(sheet, baseRow, baseRow, index, index+1, secondHeaderStyle);
                    row1.getCell(index).setCellValue(ConcludeContant.HEAR_OTHER + column);
                    row1.getCell(index).setCellStyle(secondHeaderStyle);

                    row2.getCell(index).setCellValue(ConcludeContant.SECOND_HEAR_ROW1);
                    row2.getCell(index+1).setCellValue(ConcludeContant.SECOND_HEAR_ROW2);
                }
                index+=2;
            }
            baseRow+=2;
            // 装载结果
            List<ConcludeScoreContentVO> content = concludeScoreCheckVO.getContent();
            int baseRowTemp = baseRow;
            for (int j = baseRowTemp; j < content.size()+baseRowTemp; j++) {
                HSSFRow contentRow = sheet.createRow(j);
                contentRow.setHeight((short) 400);
                ConcludeScoreContentVO concludeScoreContentVO = content.get(j - baseRowTemp);
                List<String> rowContent = concludeScoreContentVO.getRowContent();

                // 最后一行最后一列
                if( i ==  excelData.size() -1 && j == (content.size()+baseRowTemp -1) ){
                    // 创建单元格
                    for (int colum = 0; colum < size; colum++) {
                        HSSFCell cell = contentRow.createCell(colum);
                        cell.setCellStyle(cellStyle);
                    }
                    contentRow.getCell(0).setCellValue(rowContent.get(0));
                    // 合并单元格内容
                    int lastRowIndex = 1;
                    for (int k = 1; k < rowContent.size() ; k++) {

                        if( k == 1 ){
                            CellUtil.mergingCells(sheet, j, j, lastRowIndex,lastRowIndex+2, cellStyle);
                        }else{
                            CellUtil.mergingCells(sheet, j, j, lastRowIndex,lastRowIndex+1, cellStyle);
                        }
                        sheet.getRow(j).getCell(lastRowIndex).setCellValue(rowContent.get(k));

                        if( k == 1 ){
                            lastRowIndex+=3;
                        }else{
                            lastRowIndex+=2;
                        }
                    }
                }else{
                    int columIndex = 0;
                    for (int colum = 0; colum < rowContent.size(); colum++) {
                        HSSFCell cell = contentRow.createCell(columIndex);
                        String cellContent = rowContent.get(colum);
                        if(cellContent.contains("/")){
                            String[] split = cellContent.split("/");
                            cell.setCellValue(split[0]);
                            cell.setCellStyle(cellStyle);
                            columIndex++;
                            HSSFCell nextCell = contentRow.createCell(columIndex);
                            nextCell.setCellValue(split[1]);
                            nextCell.setCellStyle(cellStyle);
                        }else{
                            cell.setCellValue(rowContent.get(colum));
                            cell.setCellStyle(cellStyle);
                        }
                        columIndex++;
                    }
                }
                baseRow++;
            }
            baseRow+=1;
        }
        // 设置列宽
        setDefaultWidth(sheet, columCount);
        try{
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            StatisticExcelUtil.ExcelOutputStream("学生成绩导入", request, response, os);
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 将数据更新至系统
     * @param courseId 课程ID
     * @param studentId 学生ID
     * @param busiType 业务类型
     * @param score 学生分数
     * @param fullMark 满分
     */
    @Override
    @Transactional
    public void updateScoreFromSys(Long courseId, Long studentId, String busiType, BigDecimal score, BigDecimal fullMark) {

        // 查询出关联课程和record
        List<ConcludeStudent> sysStudents = concludeStudentMapperExt.findSysStudent(courseId, studentId);
        // 未查询到当前关系
        if(CollectionUtil.isEmpty(sysStudents)){
            return;
        }
        // 查询所有与系统关联项的考核分项信息
        List<ConcludeCheckSub> concludeCheckSubs = concludeCheckSubMapperExt.findSysSub(courseId, busiType);
        if(CollectionUtil.isEmpty(concludeCheckSubs)){
            return;
        }
        List<ConcludeScoreDTO> concludeScores = concludeScoreMapperExt.findSysScore(sysStudents, concludeCheckSubs);

        for (ConcludeScoreDTO concludeScore : concludeScores) {
            Integer subFullMark = concludeScore.getSubFullMark();
            if(subFullMark == null){
                subFullMark = 100;
            }
            BigDecimal subScore = NumberUtil.mul(NumberUtil.div(score, fullMark), subFullMark);
            concludeScore.setScore(subScore);
        }
        // 批量执行数据更新
        List<ConcludeScore> updateRecord = BeanUtil.batchTransform(ConcludeScore.class, concludeScores);
        if(CollectionUtil.isNotEmpty(updateRecord)){
            concludeScoreMapperHelper.updateBatch( updateRecord );
        }

    }

    /**
     * 插入汇总信息
     * @param concludeScoreCheckVOS
     */
    private void insertSummer(List<ConcludeScoreCheckVO> concludeScoreCheckVOS, Conclude conclude) {

        ConcludeScoreCheckVO concludeScoreSummer = new ConcludeScoreCheckVO();
        ConcludeScoreHeaderVO concludeScoreHeaderVO = new ConcludeScoreHeaderVO();
        // 存入分项目的头信息
        concludeScoreHeaderVO.setSubHeaderName("项目");
        concludeScoreHeaderVO.setCourseHeaderVO(concludeScoreCheckVOS.get(0).getHeader().getCourseHeaderVO());
        concludeScoreSummer.setHeader(concludeScoreHeaderVO);
        concludeScoreSummer.setCheckName("总评成绩/课程目标达成度");
        Optional<BigDecimal> totalGrade = concludeScoreCheckVOS.stream().map(ConcludeScoreCheckVO::getGrade).reduce(NumberUtil::add);
        Optional<BigDecimal> totalFullMark = concludeScoreCheckVOS.stream().map(concludeScoreCheckVO -> new BigDecimal(concludeScoreCheckVO.getFullMark())).reduce(NumberUtil::add);
        concludeScoreSummer.setFullMark(totalFullMark.orElseGet(()-> new BigDecimal("0")).toString());
        concludeScoreSummer.setGrade(totalGrade.orElseGet(()-> new BigDecimal("0")));

        Console.log("+++++++++++++++++++"+"执行大表中的计算"+"+++++++++++++++");
        Console.log("+++++++++++++++++++"+"执行大表中的计算"+"+++++++++++++++");
        Console.log("+++++++++++++++++++"+"执行大表中的计算"+"+++++++++++++++");
        timeChache.put(conclude.getId()+ConcludeContant.PROPERTY_SCORE , totalGrade.orElseGet(()-> new BigDecimal("0")));



        List<ConcludeScoreContentVO> result = Lists.newArrayList();
        // 合计行
        List<String> summation = Lists.newArrayList();
        // 添加达成度
        List<String> aDegreeOf = Lists.newArrayList("达成度");
        // 填充合计
        for (ConcludeScoreCheckVO concludeScoreCheckVO : concludeScoreCheckVOS) {
            // 考核分项 内容
            List<ConcludeScoreContentVO> contents = concludeScoreCheckVO.getContent();
            for (int i = 0; i <contents.size(); i++) {

                List<String> content = contents.get(i).getRowContent();
                if("合计".equals(content.get(0))){
                    if(CollectionUtil.isEmpty(summation)){
                        summation.addAll(content);
                    }else{
                        // 计算合计相加
                        computeMergeRow(summation, content);
                    }
                }
            }
        }

        for (int i = 2; i < summation.size(); i+=2) {

            BigDecimal proportion = new BigDecimal("0");
            BigDecimal standardsScore = new BigDecimal("0");
            BigDecimal realSummer = new BigDecimal("0");

            if(StrUtil.isNotBlank(summation.get(i - 1)) && StrUtil.isNotBlank(summation.get(i)) ){
                if( i == 2 ){
                    String scoreProportion = summation.get(i - 1);
                    String[] split = scoreProportion.split("/");
                    standardsScore = new BigDecimal(split[0]);  // 达标分数
                    if(StrUtil.isNotBlank(summation.get(i))){
                        realSummer = new BigDecimal(summation.get(i));
                    }
                }else{
                    BigDecimal bigDecimal = new BigDecimal(summation.get(i - 1));
                    BigDecimal thresholdValue = NumberUtil.div(conclude.getThresholdValue(), 100, ConcludeContant.RETAIN_DECIMALS);  // 阈值
                    standardsScore = NumberUtil.mul(bigDecimal, thresholdValue);  // 达标分数
                    realSummer = new BigDecimal(summation.get(i));  // 真实成绩
                }
                // 规避 0 不能做除数
                if(realSummer.compareTo(new BigDecimal("0")) != 0){
                    proportion = NumberUtil.div(realSummer, standardsScore,
                            ConcludeContant.RETAIN_DECIMALS, RoundingMode.DOWN).stripTrailingZeros(); // 达成度比重
                }
                aDegreeOf.add(proportion.toPlainString());
            }else{
                aDegreeOf.add("");
            }
        }
        List<ConcludeCourse> courseIds = concludeCourseMapperHelper.findByCondition(conclude.getId());
        String courseKey = courseIds.stream().map(course -> String.valueOf(course.getId())).collect(Collectors.joining());
        timeChache.put(courseKey, summation);

        result.add(new ConcludeScoreContentVO(false, summation )); // 添加合计
        result.add(new ConcludeScoreContentVO(false, aDegreeOf )); // 添加达成度
        concludeScoreSummer.setContent(result);
        concludeScoreCheckVOS.add(concludeScoreSummer);
    }

    /**
     * 计算合并行
     * @param summation
     * @param rowInfos
     */
    private void computeMergeRow(List<String> summation, List<String> rowInfos) {
        for (int j = 1; j < rowInfos.size(); j++) {
            // 拆解 期望/满分
            if(j == 1){
                String str = rowInfos.get(1);
                String[] split = str.split("/");
                BigDecimal expectValue = new BigDecimal(split[0]);
                BigDecimal fullValue = new BigDecimal(split[1]);

                String summationStr = summation.get(1);
                String[] summationSplit = summationStr.split("/");
                BigDecimal summationExpectValue = new BigDecimal(summationSplit[0]);
                BigDecimal summationFullValue = new BigDecimal(summationSplit[1]);

                BigDecimal resultExpectValue = NumberUtil.add(expectValue, summationExpectValue);
                BigDecimal resultFullValue = NumberUtil.add(fullValue, summationFullValue);

                summation.set(1, resultExpectValue.stripTrailingZeros().toPlainString()+"/" +resultFullValue.stripTrailingZeros().toPlainString());
            }else{
                BigDecimal subRowNumber = new BigDecimal("0");
                if(StrUtil.isNotBlank(rowInfos.get(j))){
                    subRowNumber =  new BigDecimal(rowInfos.get(j));
                }
                BigDecimal totalRowNumber = new BigDecimal("0");
                if(StrUtil.isNotBlank(summation.get(j))){
                    totalRowNumber = new BigDecimal(summation.get(j));
                }
                summation.set(j, NumberUtil.add(subRowNumber, totalRowNumber).stripTrailingZeros().toPlainString());
            }
        }
    }

    /**
     * 获取内容
     * @param concludeCheck
     * @param concludeCheckSubs
     * @param courseIds
     * @param conclude
     * @return
     */
    private List<ConcludeScoreContentVO> getContent(ConcludeCheck concludeCheck ,List<ConcludeCheckSub> concludeCheckSubs,
                                                    List<ConcludeCourseHeaderVO> courseIds, Conclude conclude) {
        // 查询总成绩和总的平均分成绩
        List<ConcludeScoreContentVO> result = Lists.newArrayList();
        BigDecimal checkWeight = NumberUtil.div(concludeCheck.getWeight(), 100, ConcludeContant.RETAIN_DECIMALS);  // 考核项
        BigDecimal thresholdValue = NumberUtil.div(conclude.getThresholdValue(), 100, ConcludeContant.RETAIN_DECIMALS); // 阈值

        for (ConcludeCheckSub concludeCheckSub : concludeCheckSubs) {

            List<String> lineInfo = Lists.newArrayList();
            ConcludeScoreContentVO concludeScoreContentVO = new ConcludeScoreContentVO(false, lineInfo);
            Long subId = concludeCheckSub.getId();

            // 是否需要设置权重值
            Boolean isSetWeight = getIsSetWeight(subId);
            concludeScoreContentVO.setIsSetWeight(isSetWeight);
            concludeScoreContentVO.setSubId(subId);


            BigDecimal fullMark = concludeCheckSub.getFullMark(); // 满分项不能为空
            if(fullMark == null){
                fullMark = new BigDecimal(100);
            }
            BigDecimal avgScoreRecord = concludeScoreMapperExt.getAvgScore(concludeCheckSub.getId(), conclude.getId());  // 平均成绩不能为空
            // 期望分 || 满分 || 成绩
            BigDecimal checkSubWeight = NumberUtil.div(concludeCheckSub.getWeight(), 100, ConcludeContant.RETAIN_DECIMALS);  // 考核分项
            BigDecimal threshold = NumberUtil.round(NumberUtil.mul(checkWeight, checkSubWeight, thresholdValue, 100), ConcludeContant.RETAIN_DECIMALS); // 期望分
            BigDecimal fullScore = NumberUtil.round(NumberUtil.mul( checkWeight, checkSubWeight, ConcludeContant.DEFAULT_SCORE ), ConcludeContant.RETAIN_DECIMALS); // 满分
            // 更新列头
            lineInfo.add(concludeCheckSub.getName());
            lineInfo.add(threshold.stripTrailingZeros().toPlainString() + "/" + fullScore.stripTrailingZeros().toPlainString());
            lineInfo.add("");// 占据成绩位置
            BigDecimal countScore = null;

            for (ConcludeCourseHeaderVO courseId : courseIds) {

                if(courseId.getConcludeCourseId() == -1){
                    continue;
                }
                // 满分项需要重新设置情况
                if(isSetWeight == true){
                    lineInfo.add("");// 满分
                    lineInfo.add("");// 成绩
                }else {
                    // 1. 查询当前考核分项和课程是否进行了关联
                    ConcludeSubCourse concludeSubCourse = concludeSubCourseMapperHelper.findBySubIdAndCourseId(subId, courseId.getConcludeCourseId());
                    // 2. 查询关联的课程权重
                    if(concludeSubCourse == null || ObjectUtil.isNull(concludeSubCourse.getCourseWeight())){
                        lineInfo.add("");// 满分
                        lineInfo.add("");// 成绩
                    }else{
                        // 满分
                        BigDecimal courseWeight = NumberUtil.div(concludeSubCourse.getCourseWeight(), new BigDecimal(100), ConcludeContant.RETAIN_DECIMALS );
                        BigDecimal subFullScore = NumberUtil.round(NumberUtil.mul(fullScore, courseWeight), ConcludeContant.RETAIN_DECIMALS);
                        lineInfo.add(subFullScore.stripTrailingZeros().toPlainString());

                        // 成绩
                        if(ObjectUtil.isNull(fullMark) || ObjectUtil.isNull(avgScoreRecord)){
                            lineInfo.add("");
                        }else{
                            BigDecimal avgScore = NumberUtil.round(NumberUtil.div(ConcludeContant.DEFAULT_SCORE, fullMark), ConcludeContant.RETAIN_DECIMALS) ;
                            avgScore = NumberUtil.round(NumberUtil.mul(avgScore, avgScoreRecord), ConcludeContant.RETAIN_DECIMALS);
                            BigDecimal realScore = NumberUtil.round(NumberUtil.mul(checkWeight, checkSubWeight, avgScore), ConcludeContant.RETAIN_DECIMALS);
                            BigDecimal subRealScore = NumberUtil.round(NumberUtil.mul(realScore, courseWeight), ConcludeContant.RETAIN_DECIMALS);
                            lineInfo.add(subRealScore.stripTrailingZeros().toPlainString());
                        }
                    }
                }

                String courseScore = lineInfo.get(lineInfo.size() - 1);
                if(StrUtil.isNotBlank(courseScore)){

                    if(countScore == null){
                        countScore = new BigDecimal("0");
                    }
                    BigDecimal bigDecimal = new BigDecimal(courseScore);
                    countScore = NumberUtil.add(countScore, bigDecimal);

                }
            }
            if(ObjectUtil.isNotNull(countScore)){
                lineInfo.set(2, countScore.stripTrailingZeros().toPlainString());
            }

            if(ObjectUtil.isNotNull(concludeCheckSub.getSysFunc())){
                concludeScoreContentVO.setIsRefSys(true);
            }
            result.add(concludeScoreContentVO);
        }
        // 填充合计内容
        List<String> total = Lists.newArrayList("合计");
        getTotal(total, result);

        return result;
    }

    /**
     * 考核分项的权重值是否需要重新设置
     * @param subId
     * @return
     */
    private Boolean getIsSetWeight(Long subId) {

        // 关联课程的权重是否等于100%
        Long sumCourseWeight = concludeSubCourseMapperExt.getCountCourseWeight(subId);

        if(ObjectUtil.isNull(sumCourseWeight)){
            return true;
        }
        if(!(sumCourseWeight.intValue() == ConcludeContant.DEFAULT_SCORE)){

            return true;
        }
        return false;
    }

    /**
     * 获取合计
     * @param total
     * @param result
     */
    private void getTotal(List<String> total, List<ConcludeScoreContentVO> result) {

        int startContent = 1;
        // 1. 结合前端图标展示从第三位开始计算
        for (int i = 0; i < result.size(); i++) {

            List<String> row = result.get(i).getRowContent();
            if(i == 0){
                // 用第一行作为默认值
                total.addAll(row.subList(startContent, row.size()));
                continue;
            }
            computeMergeRow(total, row);
        }
        ConcludeScoreContentVO mergeInfo = new ConcludeScoreContentVO(false, total);
        result.add(mergeInfo);
    }

    /**
     * 创建头信息
     */
    private ConcludeScoreHeaderVO initHeader(Long concludeId) {

        ConcludeScoreHeaderVO header = new ConcludeScoreHeaderVO();
        List<ConcludeCourseHeaderVO> headerVOS = Lists.newArrayList();
        // 标识插入第一行
        headerVOS.add(new ConcludeCourseHeaderVO(-1l));
        List<ConcludeCourse> concludeCourses = concludeCourseMapperHelper.findByCondition(concludeId);

        for (ConcludeCourse concludeCours : concludeCourses) {
            ConcludeCourseHeaderVO headerVO = new ConcludeCourseHeaderVO(concludeCours.getId());
            headerVOS.add(headerVO);
        }
        header.setCourseHeaderVO(headerVOS);
        return header;
    }



    /**
     * 保存数据到工程认证
     * @param scoreDTO
     */
    @Override
    public void saveToConclude(BusiFullMarkDTO scoreDTO) {
        this.scoreDTO = scoreDTO;

        String sysFunkKey = String.valueOf(scoreDTO.getBusiType());
        Long courseId = scoreDTO.getCourseId();
        Integer inScore = scoreDTO.getScore();
        Integer fullMark = scoreDTO.getFullMark();
        Long userId = scoreDTO.getUserId();

        if(ObjectUtil.isNotNull(inScore)){

            Long studentId = null;
            if(ObjectUtil.isNotNull(scoreDTO.getStudentId())){
                studentId = scoreDTO.getStudentId();
            }else{
                UserClass student = userClassMapperHelper.findByCondition(scoreDTO.getClassId(), userId);
                if(ObjectUtil.isNotNull(student)){
                    studentId = student.getId();
                }
            }
            if(studentId != null){
                updateScoreFromSys(courseId, studentId, sysFunkKey, new BigDecimal(inScore), new BigDecimal(fullMark));
            }
        }
    }

    @Override
    public void saveToConclude(List<BusiFullMarkDTO> lists, Long courseId, String busiType) {

        List<ConcludeStudent> concludeStudents = concludeStudentMapperExt.findSysStudentByCourseId(courseId);

        if(CollectionUtil.isEmpty(concludeStudents)){
            return;
        }
        // 工程认证中关联系统学生的信息
        List<ConcludeCheckSub> concludeCheckSubs = concludeCheckSubMapperExt.findSysSub(courseId, busiType);
        if(CollectionUtil.isEmpty(concludeCheckSubs)){
            return;
        }
        // 查询出系统已关联记录
        List<ConcludeScoreDTO> concludeScores = concludeScoreMapperExt.findSysScore(concludeStudents, concludeCheckSubs);
        // 关联到sysId 查询到得分
        List<ConcludeScoreDTO> snoPropotin = Lists.newArrayList();
        // 计算出每个学号所对应的比例值
        for (BusiFullMarkDTO busiFullMarkDTO : lists) {

            Integer score = busiFullMarkDTO.getScore();
            Integer fullMark = busiFullMarkDTO.getFullMark();
            BigDecimal propotion = NumberUtil.div(score, fullMark);
            // 学生信息
            for (ConcludeStudent concludeStudent : concludeStudents) {
                if(concludeStudent.getSysStuId().equals(busiFullMarkDTO.getStudentId())){
                    ConcludeScoreDTO concludeScoreDTO = new ConcludeScoreDTO();
                    concludeScoreDTO.setStudentNo(concludeStudent.getSno());
                    concludeScoreDTO.setProportion(propotion);
                    snoPropotin.add(concludeScoreDTO);
                }
            }
        }
        Map<String, List<ConcludeScoreDTO>> studentNos = snoPropotin.stream().collect(Collectors.groupingBy(ConcludeScoreDTO::getStudentNo));

        for (ConcludeScoreDTO concludeScore : concludeScores) {
            String studentNo = concludeScore.getStudentNo();
            if(ObjectUtil.isNotNull(studentNos.get(studentNo))){
                ConcludeScoreDTO concludeScoreDTO = studentNos.get(studentNo).get(0);
                Integer subFullMark = concludeScore.getSubFullMark();
                if(subFullMark == null){ // 默认分值为100
                    subFullMark = 100;
                }
                BigDecimal score = NumberUtil.mul(concludeScoreDTO.getProportion(), subFullMark);
                concludeScore.setScore(score);
            }
        }
        // 执行数据更新
        List<ConcludeScore> updateRecord = BeanUtil.batchTransform(ConcludeScore.class, concludeScores);
        if(CollectionUtil.isNotEmpty(updateRecord)){
            concludeScoreMapperHelper.updateBatch( updateRecord );
        }

    }



}
